﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Lookstone.Data;
using Lookstone.Data.SQLite;

namespace Weber.CS3280.Inventory.Data
{
	public class ItemCategory : SQLiteInt32Key
	{
		private Model.ItemCategory _category;

		public ItemCategory()
			: base()
		{
			_category = new Model.ItemCategory()
				{
					Name = string.Empty,
				};
		}

		public ItemCategory(Model.ItemCategory source)
			: base()
		{
			_category = new Model.ItemCategory()
				{
					ItemCategoryId = source.ItemCategoryId,
					Name = source.Name ?? string.Empty,
				};

			InternalID = _category.ItemCategoryId ?? 0;
			IsNewRecord = InternalID == 0;
		}

		public ItemCategory(InventoryConnectionContext context, int id)
			: base(context, id)
		{
		}

		public ItemCategory(RowReader reader)
			: base(reader)
		{
		}

		protected override void LoadRowData(Lookstone.Data.RowReader reader)
		{
			InternalID = reader.GetField<int>("ItemCategoryId");

			_category = new Model.ItemCategory()
			{
				ItemCategoryId = InternalID,
				Name = reader.GetField<string>("Name"),
			};
		}

		protected override SQLiteCommand GetSaveCommand()
		{
			SQLiteCommand cmd = new SQLiteCommand() { CommandType = CommandType.Text };

			if (IsNewRecord)
				cmd.CommandText = GetInsertQuery();
			else
			{
				cmd.CommandText = GetUpdateQuery();
				cmd.Parameters.Add("@ItemCategoryId", DbType.Int32).Value = ID;
			}

			cmd.Parameters.Add("@Name", DbType.String).Value = _category.Name ?? string.Empty;
			return cmd;
		}

		public string Name
		{
			get { return _category.Name; }
			set { _category.Name = value; }
		}

		#region //****** Queries ******//
		private static string GetInsertQuery()
		{
			return
				"INSERT INTO " +
					"ItemCategory " +
				"( " +
					"Name " +
				") " +
				"VALUES " +
				"( " +
					"@Name " +
				"); " +
				"SELECT last_insert_rowid() AS ItemCategoryId ";
		}

		private static string GetSelectAllQuery()
		{
			return
				"SELECT " +
					 "ItemCategoryId " +
					",Name " +
				"FROM " +
					"ItemCategory " +
				"ORDER BY " +
					"Name Asc ";
		}

		private static string GetSelectByNameQuery()
		{
			return
				"SELECT " +
					 "ItemCategoryId " +
					",Name " +
				"FROM " +
					"ItemCategory " +
				"WHERE " +
					"Name LIKE @NameFilter " +
				"ORDER BY " +
					"Name Asc ";
		}

		private static string GetUpdateQuery()
		{
			return
				"UPDATE " +
					"ItemCategory " +
				"SET " +
					"Name = @Name " +
				"WHERE " +
					"ItemCategoryId = @ItemCategoryId ";
		}
		#endregion //****** Queries ******//

		public static Model.ItemCategory[] GetItemCategoriesByName(InventoryConnectionContext context, string name)
		{
			using (SQLiteCommand cmd = new SQLiteCommand(GetSelectByNameQuery()) { CommandType = CommandType.Text })
			{
				context.ConnectCommand(cmd);
				cmd.Parameters.AddWithValue("@NameFilter", string.Format("%{0}%", name));
				return LoadBulk<ItemCategory>(cmd)
					.Select(c => c._category)
					.ToArray();
			}
		}

		public static Model.ItemCategory[] GetAllItemCategories(InventoryConnectionContext context)
		{
			using (SQLiteCommand cmd = new SQLiteCommand(GetSelectAllQuery()) { CommandType = CommandType.Text })
			{
				context.ConnectCommand(cmd);
				return LoadBulk<ItemCategory>(cmd)
					.Select(c => c._category)
					.ToArray();
			}
		}
	}
}
